package com.example.campusforumapi.dao;

import com.example.campusforumapi.entity.Activity;
import com.example.campusforumapi.entity.Article;
import com.example.campusforumapi.entity.DepartmentStats;
import com.example.campusforumapi.entity.WeeklyStats;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface StatsMapper {

@Select("SELECT title,username,departmentName\n" +
        "FROM article,user,department\n" +
        "WHERE user.user_id=article.user_id and article.department_id=department.department_id and publishTime >= DATE_SUB(NOW(), INTERVAL 7 DAY)\n" +
        "ORDER BY hot_score DESC")
     List<Article> getTopArticleList();


    List<WeeklyStats> getWeeklyRegistrations(LocalDateTime start, LocalDateTime end);

    List<DepartmentStats> getDepartmentUsers();

    @Select("select gender as name,COUNT(user_id) AS value from user GROUP BY gender")
    List<DepartmentStats> getGenderUsers();
    @Select("SELECT title,username,departmentName\n" +
            "FROM activity,user,department\n" +
            "WHERE user.user_id=activity.user_id and activity.department_id=department.department_id and publishTime >= DATE_SUB(NOW(), INTERVAL 7 DAY)\n" +
            "ORDER BY hot_score DESC")
    List<Activity> getTopActivityList();
@Select("select COUNT(article_id) as value,categoryName as name \n" +
        "from article,articlecategory\n" +
        "where article.category_id=articlecategory.category_id\n" +
        "GROUP BY article.category_id\n" +
        "ORDER BY article.category_id ")
    List<DepartmentStats> getArticleTags();
@Select("select COUNT(activity_id) as value,tagName as name \n" +
        "from activity,activitytag\n" +
        "where activity.tag_id=activitytag.tag_id\n" +
        "GROUP BY activity.tag_id\n" +
        "ORDER BY activity.tag_id ")
    List<DepartmentStats> getActivityTags();

}
